package cn.iocoder.yudao.module.biz.utils;

import cn.iocoder.yudao.framework.security.core.util.SecurityFrameworkUtils;
import cn.iocoder.yudao.module.biz.controller.admin.risk.controlMeasures.vo.BizControlMeasuresCreateReqVO;
import cn.iocoder.yudao.module.biz.controller.admin.risk.hazardResource.vo.BizHazardResourceCreateReqVO;
import cn.iocoder.yudao.module.biz.controller.admin.risk.riskInfo.vo.BizRiskInfoCreateReqVO;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.ArrayList;
import java.util.List;

public class ExcelRisk {
    public List<BizRiskInfoCreateReqVO> addReportByExcel(Workbook wb)
    {
        String message = "Import success";
        int rowIndex = 0;
        try {
            Sheet sheet = wb.getSheetAt(0);    //获得第一个表单
            List<CellRangeAddress> cras = getCombineCell(sheet);
            int count = sheet.getLastRowNum()+1;//总行数
            //风险列表
            List<BizRiskInfoCreateReqVO> riskInfoCreateReqVOS = new ArrayList<>();
            for(int i = 3; i < count;i++){
                rowIndex = i;
                Row row = sheet.getRow(i);
                BizRiskInfoCreateReqVO riskInfoCreateReqVO = new BizRiskInfoCreateReqVO();
                //风险点类型
                if(getCellValue(row.getCell(0)).contains("基础")){
                    riskInfoCreateReqVO.setRiskType(1);
                } else if (getCellValue(row.getCell(0)).contains("作业")) {
                    riskInfoCreateReqVO.setRiskType(2);
                } else if (getCellValue(row.getCell(0)).contains("设备")) {
                    riskInfoCreateReqVO.setRiskType(3);
                } else {

                }
                //风险点名称
                riskInfoCreateReqVO.setRiskPoint(getCellValue(row.getCell(1)));
                riskInfoCreateReqVO.setCompanyId(SecurityFrameworkUtils.getLoginUser().getDeptId()+"");
                //责任单位
                riskInfoCreateReqVO.setResponsibleId(getCellValue(row.getCell(2)));
                //责任人
                riskInfoCreateReqVO.setDirectPersonName(getCellValue(row.getCell(3)));
                //危险源列表
                List<BizHazardResourceCreateReqVO> bizHazardResourceCreateReqVOS = new ArrayList<>();
                if(isMergedRegion(sheet,i,0)){
                    int lastRow = getRowNum(cras,sheet.getRow(i).getCell(0),sheet);
                    for(;i<=lastRow;i++){
                        row = sheet.getRow(i);
                        //危险源
                        BizHazardResourceCreateReqVO hazardResourceCreateReqVO = new BizHazardResourceCreateReqVO();
                        //作业步骤
                        hazardResourceCreateReqVO.setOperationSteps(getCellValue(row.getCell(4)));
                        //危险源或潜在事件
                        hazardResourceCreateReqVO.setHazardEvent(getCellValue(row.getCell(5)));
                        //风险分级
                        if(getCellValue(row.getCell(6)).contains("低")) {
                            hazardResourceCreateReqVO.setRiskLevel("1");
                        } else if(getCellValue(row.getCell(6)).contains("一般")) {
                            hazardResourceCreateReqVO.setRiskLevel("2");
                        } else if(getCellValue(row.getCell(6)).contains("较大")) {
                            hazardResourceCreateReqVO.setRiskLevel("3");
                        } else if(getCellValue(row.getCell(6)).contains("重大")) {
                            hazardResourceCreateReqVO.setRiskLevel("4");
                        } else {

                        }
                        //可能发生的事故类型及后果
                        hazardResourceCreateReqVO.setMainConsequences(getCellValue(row.getCell(7)));
                        //管控措施列表
                        List<BizControlMeasuresCreateReqVO> controlMeasuresCreateReqVOS = new ArrayList<>();
                        //工程技术
                        BizControlMeasuresCreateReqVO controlVO1 = new BizControlMeasuresCreateReqVO();
                        controlVO1.setControlType("1");
                        controlVO1.setControlMeasures(getCellValue(row.getCell(8)));
                        controlMeasuresCreateReqVOS.add(controlVO1);
                        //管理措施
                        BizControlMeasuresCreateReqVO controlVO2 = new BizControlMeasuresCreateReqVO();
                        controlVO2.setControlType("2");
                        controlVO2.setControlMeasures(getCellValue(row.getCell(9)));
                        controlMeasuresCreateReqVOS.add(controlVO2);
                        //培训教育
                        BizControlMeasuresCreateReqVO controlVO3 = new BizControlMeasuresCreateReqVO();
                        controlVO3.setControlType("3");
                        controlVO3.setControlMeasures(getCellValue(row.getCell(10)));
                        controlMeasuresCreateReqVOS.add(controlVO3);
                        //个体防护
                        BizControlMeasuresCreateReqVO controlVO4 = new BizControlMeasuresCreateReqVO();
                        controlVO4.setControlType("4");
                        controlVO4.setControlMeasures(getCellValue(row.getCell(11)));
                        controlMeasuresCreateReqVOS.add(controlVO4);
                        //应急处理
                        BizControlMeasuresCreateReqVO controlVO5 = new BizControlMeasuresCreateReqVO();
                        controlVO5.setControlType("5");
                        controlVO5.setControlMeasures(getCellValue(row.getCell(12)));
                        controlMeasuresCreateReqVOS.add(controlVO5);
                        hazardResourceCreateReqVO.setControlMeasuresCreateReqVOList(controlMeasuresCreateReqVOS);
                        //管控层级
                        if(getCellValue(row.getCell(13)).contains("班组")) {
                            hazardResourceCreateReqVO.setControlLevel(1);
                        } else if(getCellValue(row.getCell(13)).contains("部门")){
                            hazardResourceCreateReqVO.setControlLevel(2);
                        } else if(getCellValue(row.getCell(13)).contains("岗位")){
                            hazardResourceCreateReqVO.setControlLevel(3);
                        } else {

                        }
                        //责任单位
                        hazardResourceCreateReqVO.setBusinessUnit(getCellValue(row.getCell(14)));
                        //责任人
                        hazardResourceCreateReqVO.setDutyPerson(getCellValue(row.getCell(15)));
                        //hazardResourceCreateReqVO.setPersonLiable(getCellValue(row.getCell(15)));
                        //备注
                        hazardResourceCreateReqVO.setRemarks(getCellValue(row.getCell(16)));
                        bizHazardResourceCreateReqVOS.add(hazardResourceCreateReqVO);
                    }
                    i--;
                }else{
                    row = sheet.getRow(i);
                    //危险源
                    BizHazardResourceCreateReqVO hazardResourceCreateReqVO = new BizHazardResourceCreateReqVO();
                    //作业步骤
                    hazardResourceCreateReqVO.setOperationSteps(getCellValue(row.getCell(4)));
                    //危险源或潜在事件
                    hazardResourceCreateReqVO.setHazardEvent(getCellValue(row.getCell(5)));
                    //风险分级
                    if(getCellValue(row.getCell(6)).contains("低")) {
                        hazardResourceCreateReqVO.setRiskLevel("1");
                    } else if(getCellValue(row.getCell(6)).contains("一般")) {
                        hazardResourceCreateReqVO.setRiskLevel("2");
                    } else if(getCellValue(row.getCell(6)).contains("较大")) {
                        hazardResourceCreateReqVO.setRiskLevel("3");
                    } else if(getCellValue(row.getCell(6)).contains("重大")) {
                        hazardResourceCreateReqVO.setRiskLevel("4");
                    } else {

                    }
                    //可能发生的事故类型及后果
                    hazardResourceCreateReqVO.setMainConsequences(getCellValue(row.getCell(7)));
                    //管控措施列表
                    List<BizControlMeasuresCreateReqVO> controlMeasuresCreateReqVOS = new ArrayList<>();
                    //工程技术
                    BizControlMeasuresCreateReqVO controlVO1 = new BizControlMeasuresCreateReqVO();
                    controlVO1.setControlType("1");
                    controlVO1.setControlMeasures(getCellValue(row.getCell(8)));
                    controlMeasuresCreateReqVOS.add(controlVO1);
                    //管理措施
                    BizControlMeasuresCreateReqVO controlVO2 = new BizControlMeasuresCreateReqVO();
                    controlVO2.setControlType("2");
                    controlVO2.setControlMeasures(getCellValue(row.getCell(9)));
                    controlMeasuresCreateReqVOS.add(controlVO2);
                    //培训教育
                    BizControlMeasuresCreateReqVO controlVO3 = new BizControlMeasuresCreateReqVO();
                    controlVO3.setControlType("3");
                    controlVO3.setControlMeasures(getCellValue(row.getCell(10)));
                    controlMeasuresCreateReqVOS.add(controlVO3);
                    //个体防护
                    BizControlMeasuresCreateReqVO controlVO4 = new BizControlMeasuresCreateReqVO();
                    controlVO4.setControlType("4");
                    controlVO4.setControlMeasures(getCellValue(row.getCell(11)));
                    controlMeasuresCreateReqVOS.add(controlVO4);
                    //应急处理
                    BizControlMeasuresCreateReqVO controlVO5 = new BizControlMeasuresCreateReqVO();
                    controlVO5.setControlType("5");
                    controlVO5.setControlMeasures(getCellValue(row.getCell(12)));
                    controlMeasuresCreateReqVOS.add(controlVO5);
                    hazardResourceCreateReqVO.setControlMeasuresCreateReqVOList(controlMeasuresCreateReqVOS);
                    //管控层级
                    if(getCellValue(row.getCell(13)).contains("班组")) {
                        hazardResourceCreateReqVO.setControlLevel(1);
                    } else if(getCellValue(row.getCell(13)).contains("部门")){
                        hazardResourceCreateReqVO.setControlLevel(2);
                    } else if(getCellValue(row.getCell(13)).contains("岗位")){
                        hazardResourceCreateReqVO.setControlLevel(3);
                    } else {

                    }
                    //责任单位
                    hazardResourceCreateReqVO.setBusinessUnit(getCellValue(row.getCell(14)));
                    //责任人
                    hazardResourceCreateReqVO.setDutyPerson(getCellValue(row.getCell(15)));
                    //备注
                    hazardResourceCreateReqVO.setRemarks(getCellValue(row.getCell(16)));
                    bizHazardResourceCreateReqVOS.add(hazardResourceCreateReqVO);
                }
                riskInfoCreateReqVO.setHazardResourceCreateReqVOList(bizHazardResourceCreateReqVOS);
                riskInfoCreateReqVOS.add(riskInfoCreateReqVO);

            }
            return riskInfoCreateReqVOS;
        } catch (Exception ex) {
            return null;
        }
    }

    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public String getCellValue(Cell cell){
        if(cell == null){
            return "";
        }
        if(cell.getCellType() == Cell.CELL_TYPE_STRING){
            return cell.getStringCellValue();
        }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
            return String.valueOf(cell.getBooleanCellValue());
        }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
            return cell.getCellFormula() ;
        }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
            return String.valueOf(cell.getNumericCellValue());
        }
        return "";
    }
    /**
     * 合并单元格处理,获取合并行
     * @param sheet
     * @return List<CellRangeAddress>
     */
    public List<CellRangeAddress> getCombineCell(Sheet sheet)
    {
        List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();
        //获得一个 sheet 中合并单元格的数量
        int sheetmergerCount = sheet.getNumMergedRegions();
        //遍历所有的合并单元格
        for(int i = 0; i<sheetmergerCount;i++)
        {
            //获得合并单元格保存进list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
        return list;
    }

    private int getRowNum(List<CellRangeAddress> listCombineCell,Cell cell,Sheet sheet){
        int xr = 0;
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;
        for(CellRangeAddress ca:listCombineCell)
        {
            //获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR)
            {
                if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC)
                {
                    xr = lastR;
                }
            }

        }
        return xr;

    }
    /**
     * 判断单元格是否为合并单元格，是的话则将单元格的值返回
     * @param listCombineCell 存放合并单元格的list
     * @param cell 需要判断的单元格
     * @param sheet sheet
     * @return
     */
    public String isCombineCell(List<CellRangeAddress> listCombineCell,Cell cell,Sheet sheet)
            throws Exception{
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;
        String cellValue = null;
        for(CellRangeAddress ca:listCombineCell)
        {
            //获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR)
            {
                if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC)
                {
                    Row fRow = sheet.getRow(firstR);
                    Cell fCell = fRow.getCell(firstC);
                    cellValue = getCellValue(fCell);
                    break;
                }
            }
            else
            {
                cellValue = "";
            }
        }
        return cellValue;
    }

    /**
     * 获取合并单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public String getMergedRegionValue(Sheet sheet ,int row , int column){
        int sheetMergeCount = sheet.getNumMergedRegions();

        for(int i = 0 ; i < sheetMergeCount ; i++){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell) ;
                }
            }
        }

        return null ;
    }


    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet
     * @param row 行下标
     * @param column 列下标
     * @return
     */
    private boolean isMergedRegion(Sheet sheet,int row ,int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    return true;
                }
            }
        }
        return false;
    }

}